package com.fjnu.client.dao;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.fjnu.client.entity.po.PlantManagePO;
import com.fjnu.client.entity.vo.BatchDetailVO;
import com.fjnu.client.entity.vo.PlantBatchVO;
import com.fjnu.client.entity.vo.HistoryBatchDetailVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface PlantManageDao extends BaseMapper<PlantManagePO> {

    @Select("SELECT " +
            " batch.num batch_num, " +
            " batch.plan_id, " +
            " variety.`name` plan_name, " +
            " batch.start_time, " +
            " batch.end_time, " +
            " farmland1.type " +
            "FROM " +
            " iwea_plant_batch batch, " +
            " iwea_variety variety, " +
            " iwea_farmland farmland1, " +
            " iwea_plant_history history, " +
            " ( " +
            "SELECT DISTINCT " +
            " ( farm.id ) farm_id  " +
            "FROM " +
            " iwea_farmer farmer, " +
            " iwea_farmland farmland, " +
            " iwea_farm farm  " +
            "WHERE " +
            " farmer.id = farmland.farmer  " +
            " AND farmer.login = #{Login} " +
            " AND farmland.farm_id = farm.id  " +
            " ) tmp  " +
            "WHERE " +
            " batch.farm_id = tmp.farm_id  " +
            " AND batch.`status` = #{Status} " +
            " AND batch.variety_id = variety.id  " +
            " AND farmland1.farm_id in (tmp.farm_id) " +
            " and batch.id = history.batch_id " +
            " and history.farmland_id = farmland1.id ")
    public List<PlantBatchVO> getPlantBatchMain(@Param("Login") int login,@Param("Status") int status);

    @Select("SELECT " +
            " farmland.num farmland_num, " +
            " farmland.type, " +
            " farmer.`name`, " +
            " farmland.area, " +
            " farmland.deepth, " +
            " variety.`name` variety_name, " +
            " batch.`status` batch_status, " +
            " batch.num batch_num, " +
            " batch.start_time, " +
            " batch.end_time, " +
            " batch.plant_count, " +
            " batch.growth_stage, " +
            " batch.growth_status, " +
            " variety.describetion, " +
            " ( SELECT count( iwea_harvest.id ) FROM iwea_harvest WHERE iwea_harvest.farmland_id = farmland.id AND iwea_harvest.farm_id = farmland.farm_id ) batch_count, " +
            " ( SELECT sum( iwea_harvest.count ) FROM iwea_harvest WHERE iwea_harvest.farmland_id = farmland.id AND iwea_harvest.farm_id = farmland.farm_id ) batch_sum  " +
            "FROM " +
            " iwea_plant_batch batch, " +
            " iwea_plant_history history, " +
            " iwea_farmland farmland, " +
            " iwea_farmer farmer, " +
            " iwea_variety variety  " +
            "WHERE " +
            " batch.num =#{BatchNum} " +
            " AND batch.farm_id = history.farm_id  " +
            " AND batch.id = history.batch_id  " +
            " AND history.farmland_id = farmland.id  " +
            " AND batch.plan_id = variety.id  " +
            " AND farmland.farmer = farmer.id")
    public  List<BatchDetailVO> getPresentPlantBatchDetail(@Param("BatchNum") String batchNum);

    @Select("SELECT " +
            " batch.num batch_num, " +
            " variety.`name` variety_name, " +
            " farmland.`name` farmland_name, " +
            " batch.start_time, " +
            " batch.end_time " +
            " FROM " +
            " iwea_plant_batch batch, " +
            " iwea_plant_history history, " +
            " iwea_farmland farmland, " +
            " iwea_variety variety " +
            " WHERE " +
            " batch.num = #{BatchNum} " +
            " AND batch.farm_id = history.farm_id " +
            " AND batch.id = history.batch_id " +
            " AND history.farmland_id = farmland.id " +
            " AND batch.variety_id = variety.id")
    public  List<HistoryBatchDetailVO> getHistoryPlantBatchDetail(@Param("BatchNum") String batchNum);
}
